package Core;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author jc156563
 */

import java.sql.*;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

public class ConnectionToDatabase {

    public boolean editItemQuantity(String itemName, String itemQty) {
        Connection con = null;
        PreparedStatement query = null;

        con = connect();
        try {
            query = con.prepareStatement("UPDATE inventory SET item_qty=? WHERE item_name=?");
            query.setString(1, itemQty);
            query.setString(2, itemName);
            query.executeUpdate();
        } catch (SQLException ex) {
            return false;
        }
        return true;
    }
    public String getInventoryQuantity(String itemName)
    {
        String data = "";
        Connection con = null;
        PreparedStatement query = null;
        ResultSet holder = null;

        con = connect();
        try {
            query = con.prepareStatement("SELECT item_qty from inventory where item_name=?");
            query.setString(1, itemName);
            holder = query.executeQuery();
            holder.next();
            data = holder.getString("item_qty");
        } catch (SQLException e) {
            System.err.println("Exception: " + e.getMessage());
        }

        return data;
    }

    public boolean addItem(String itemName, String itemQty)
    {
        Connection con = null;
        PreparedStatement query = null;

        if (itemName.equals(""))
        {
            return false;
        }

        con = connect();
        if(!valueCheck(itemName))
        {
            try {
                query = con.prepareStatement("INSERT INTO inventory VALUES (NULL, ?, ?)");
                query.setString(1, itemName);
                query.setString(2, itemQty);
                query.executeUpdate();               
             } catch (SQLException ex) {
                    return false;
                }
        }
        else
        {
            return false;
        }

        return true;
    }

    public boolean deleteItem(Object selected)
    {
        Connection con = null;
        PreparedStatement query = null;
        System.out.println(selected.toString());

        con = connect();

        try {
            query = con.prepareStatement("DELETE FROM inventory WHERE item_name=?");
            query.setString(1, selected.toString());
            query.executeUpdate();
            return true;
            } catch (SQLException ex) {
            return false;

        }
    }

    public boolean editItemName(String oldName, String newName)
    {
        Connection con = null;
        PreparedStatement query = null;

        con = connect();
        try {
            query = con.prepareStatement("UPDATE inventory SET item_name=? WHERE item_name=?");
            query.setString(1, newName);
            query.setString(2, oldName);
            query.executeUpdate();
        } catch (SQLException ex) {
            return false;
        }
        return true;
    }

    public Vector getInventoryFull() {
        Vector<Object> data = new <Object>Vector();
        Vector<String> rowData = new <String>Vector();
        Connection con = null;
        Statement stmt = null;
        ResultSet resultQuery = null;


        con = connect();
        try {
            stmt = con.createStatement();
            resultQuery = stmt.executeQuery("SELECT * from inventory");

            while (resultQuery.next()) {
                rowData.addElement(resultQuery.getString("item_id"));
                rowData.addElement(resultQuery.getString("item_name"));
                rowData.addElement(resultQuery.getString("item_qty"));
                //data.addElement(rowData);
                data.addElement(rowData.clone());
                rowData.removeAllElements();
            }
        } catch (SQLException e) {
        }

        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
        }

        return data;
    }

    public Vector getInventoryNames() {
        Vector<String> data = new <String>Vector();
        Connection con = null;
        Statement stmt = null;
        ResultSet resultQuery = null;

        con = connect();
        try {
            stmt = con.createStatement();
            resultQuery = stmt.executeQuery("SELECT * from inventory");
            while (resultQuery.next()) {
                data.addElement(resultQuery.getString("item_name"));
            }
        } catch (SQLException e) {
        }
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
        }

        return data;
    }

   private boolean valueCheck(String itemName)
    {
        Connection con = null;
        PreparedStatement query = null;
        ResultSet holder = null;

        con = connect();

        try {
            query = con.prepareStatement("SELECT item_name from inventory WHERE item_name=?");
            query.setString(1, itemName);
            holder = query.executeQuery();
            holder.next();
            if (holder.getString("item_name").equals(itemName))
            {
                return true;
            }
            else
            {
                return false;
            }
            

        } catch (SQLException e) {
            System.err.println("Exception: " + e.getMessage());
            return false;
        }
        
        
    }

    private Connection connect() {
        Connection con = null;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://dunk.it.jcu.edu.au/3120S109g04";
        String dbName = "3120S109g04";
        String dbPass = "b39b80698b";

        try {
            Class.forName(driver).newInstance();
            con = DriverManager.getConnection(url, dbName, dbPass);

            if (!con.isClosed()) {
                System.out.println("Successfully connected");

            }

        } catch (Exception e) {
            System.err.println("Exception: " + e.getMessage());
        }

        return con;
    }

    public static void ConnectionToDatabase() {
    }

    
}
